﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using syc.Helper;
namespace syc.Sale
{
    public partial class 原辅料及资金安排计划 : System.Web.UI.Page
    {
        protected int month;
        protected int year;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Year0.SelectedValue = DateTime.Today.Year.ToString();
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();               
            }           
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            if (this.Month0.SelectedValue == "一季度")
            {
                string sql1 = @"select 0 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量,原料采购计划0.单价,金额 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 原料ID,SUM(重量) as 库存 from 原料库存 group by 原料ID) t1
on t0.ID=t1.原料ID  where 年度=@年度 and 月份 =13
union 
select 1 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量,辅料采购计划0.单价,金额 from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(实收) as 库存 from 材料入出库台账 group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=13";
                sql1 = sql1.Replace("@年度", this.Year0.SelectedValue);              
                this.SqlDataSource1.SelectCommand = sql1;
            }else
            if (this.Month0.SelectedValue == "二季度")
            {
                string sql1 = @"select 0 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量,原料采购计划0.单价,金额 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 原料ID,SUM(重量) as 库存 from 原料库存 group by 原料ID) t1
on t0.ID=t1.原料ID  where 年度=@年度 and 月份=14
union 
select 1 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量,辅料采购计划0.单价,金额 from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(实收) as 库存 from 材料入出库台账 group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=14 ";
                sql1 = sql1.Replace("@年度", this.Year0.SelectedValue);
                this.SqlDataSource1.SelectCommand = sql1;
            }else
            if (this.Month0.SelectedValue == "三季度")
            {
                string sql1 = @"select 0 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量,原料采购计划0.单价,金额 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 原料ID,SUM(重量) as 库存 from 原料库存 group by 原料ID) t1
on t0.ID=t1.原料ID  where 年度=@年度 and 月份=15
union 
select 1 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量,辅料采购计划0.单价,金额 from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(实收) as 库存 from 材料入出库台账 group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=15 ";
                sql1 = sql1.Replace("@年度", this.Year0.SelectedValue);
                this.SqlDataSource1.SelectCommand = sql1;
            }else
                if (this.Month0.SelectedValue == "四季度")
                {
                    string sql1 = @"select 0 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量,原料采购计划0.单价,金额 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 原料ID,SUM(重量) as 库存 from 原料库存 group by 原料ID) t1
on t0.ID=t1.原料ID  where 年度=@年度 and 月份=16
union 
select 1 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量,辅料采购计划0.单价,金额 from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(实收) as 库存 from 材料入出库台账 group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=16 ";
                    sql1 = sql1.Replace("@年度", this.Year0.SelectedValue);
                    this.SqlDataSource1.SelectCommand = sql1;
                }
                else
                {

                    string sql = @"select 0 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量,原料采购计划0.单价,金额 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 原料ID,SUM(重量) as 库存 from 原料库存 group by 原料ID) t1
on t0.ID=t1.原料ID  where 年度=@年度 and 月份=@月份
union 
select 1 as tip,年度,月份,品名,规格,单位,库存,计划购进量,单价,金额 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量,辅料采购计划0.单价,金额 from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(实收) as 库存 from 材料入出库台账 group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=@月份 ";
                    sql = sql.Replace("@年度", this.Year0.SelectedValue);
                    sql = sql.Replace("@月份", this.Month0.SelectedValue);
                    this.SqlDataSource1.SelectCommand = sql;
                }          
            this.ViewState["SelectCommand"] = this.SqlDataSource1.SelectCommand;
        }
        
        protected void Button_Save_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
            Response.Charset = "utf-8";
            // If you want the option to open the Excel file without saving than
            // comment out the line below
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.xls";
            //设置EXCEL的表头信息
            var title =this.Month0.SelectedItem.Text+"月份主要原、辅料及资金安排计划表";
            HttpContext.Current.Response.Write(
    @"<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.0 Transitional//EN"" ""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"">
<html>
<head>
<meta http-equiv=""Content-Language"" content=""zh-cn""/>
<meta http-equiv=""Content-Type"" content="""
    + HttpContext.Current.Response.ContentType + "; charset=utf-8\"/></head><body><h2><center>" + title + "</center></h2>");
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            GridView1.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            HttpContext.Current.Response.Write("</body></html>");
            Response.End();

        }

        //为了导出而重载的一个事件，具体看EXPORT TO EXCEL的文章
        public override void VerifyRenderingInServerForm(Control control)
        {

            // Confirms that an HtmlForm control is rendered for the
            //specified ASP.NET server control at run time.
        }
    }
}
